--start "E:\Link to UTBM\BD50\Projet_Web_Agenda\BD50_V2\7xx_dba\710_create_pqb_db_indisponibilite.sql"

create or replace
package body db_indisponibilite as

	PROCEDURE addTo
		(
		v_ind_date_deb        IN  varchar2
		,v_ind_date_fin        IN  varchar2
		,v_ind_jour_complet    IN  NUMBER 	default 1
		,v_ind_heure_deb       IN  varchar2 default null
		,v_ind_heure_fin       IN  varchar2 default null
		)
	IS
		v_UTI_LOGIN UTILISATEUR.UTI_LOGIN%type :=  session_common.get_context('uti_login') ;
	BEGIN
		INSERT INTO
		INDISPONIBILITE 
		(
			IND_ID
			, UTI_LOGIN
			, ind_date_deb
			, ind_date_fin
			, ind_jour_complet
			, ind_heure_deb
			, ind_heure_fin 
		)
		VALUES
		(
			SEQ_IND_ID.nextval
			,v_UTI_LOGIN
			,to_date(v_ind_date_deb,'DD/MM/YYYY')
			,to_date(v_ind_date_fin,'DD/MM/YYYY') 
			,v_ind_jour_complet
			,to_date(v_ind_heure_deb,'HH24:MI') 
			,to_date(v_ind_heure_fin,'HH24:MI')
		);

	END addTo ;

	PROCEDURE del
	(
		v_ind_id       IN  INDISPONIBILITE.IND_ID%TYPE
	)
	IS
		v_UTI_LOGIN UTILISATEUR.UTI_LOGIN%type := session_common.get_context('uti_login') ;
	BEGIN
		DELETE FROM
			INDISPONIBILITE
		WHERE
			IND_ID = v_ind_id
		and
			UTI_LOGIN = v_UTI_LOGIN;
	END del;

	
	PROCEDURE upd
	( 
		v_IND_ID            IN INDISPONIBILITE.IND_ID%TYPE
		,v_ind_date_deb        IN  varchar2
		,v_ind_date_fin        IN  varchar2
		,v_ind_jour_complet    IN  NUMBER 	default 1
		,v_ind_heure_deb       IN  varchar2 default null
		,v_ind_heure_fin       IN  varchar2 default null
	)
	IS
		v_UTI_LOGIN UTILISATEUR.UTI_LOGIN%type := session_common.get_context('uti_login') ;
	BEGIN
		UPDATE INDISPONIBILITE
			set ind_date_deb = to_date(v_ind_date_deb,'DD/MM/YYYY')
			,ind_date_fin = to_date(v_ind_date_fin,'DD/MM/YYYY')
			,ind_jour_complet = v_ind_jour_complet
			,ind_heure_deb = to_date(v_ind_heure_deb,'HH24:MI') --TODO current date saved !
			,ind_heure_fin = to_date(v_ind_heure_fin,'HH24:MI') 
		WHERE 
			IND_ID = v_IND_ID
		and 
			UTI_LOGIN = v_UTI_LOGIN;
	END upd ;

	
	function get_all(login	varchar2) 
		return sqlcur as 
		resultat sqlcur;
		begin
		open resultat for
			select
				IND_ID
				, UTI_LOGIN
				,to_char(ind_date_deb, 'DD/MM/YYYY') as ind_date_deb
				,to_char(ind_date_fin, 'DD/MM/YYYY') as ind_date_fin
				,ind_jour_complet
				,to_char(ind_heure_deb,'HH24:MI') as ind_heure_deb
				,to_char(ind_heure_fin,'HH24:MI') as ind_heure_fin
			from
				INDISPONIBILITE
			where
				UTI_LOGIN = login
			order by 1;
		return resultat;
	   
		EXCEPTION
		WHEN NO_DATA_FOUND
		THEN
		   /* Return an empty record. */
		   RETURN resultat;
	end;
	
	
end db_indisponibilite;
/
commit;